﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace Cashier
{
    class DatabaseServices : IDisposable
    {
        public AccessDB accessdb = new AccessDB();
        public MySQLDB mysqldb = new MySQLDB();
        public AccessDB accessdb2 = new AccessDB();
        /// <summary>
        /// Login service
        /// </summary>
        /// <param name="username">username to login</param>
        /// <param name="password">password for login</param>
        /// <returns>If login successed then return True; else return False</returns>
        public bool Login(string username, string password)
        {
            DataSet ds = accessdb.ExeQuery(string.Format("select * from employee where username='{0}'", username));
            if (ds.Tables[0].Rows.Count > 0)
            {
                GlobalVar.CurrentLogin = new CurrentEmployee()
                {
                    Id = (int)ds.Tables[0].Rows[0]["id"],
                    EmployeeName = ds.Tables[0].Rows[0]["name"].ToString()
                };
                return true;
            }
            return false;
        }

        /// <summary>
        /// Fill data to the data grid view control
        /// </summary>
        /// <param name="query">Query Statement</param>
        /// <param name="gridView">Data grid view control</param>
        public void FillDataToGrid(string query, DataGridView gridView)
        {
            accessdb.ExSQLtoDGV(query, gridView);
        }

        /// <summary>
        /// Get the list employees name by employee type
        /// </summary>
        /// <param name="_employeeType"></param>
        /// <returns></returns>
        public List<string> GetListEmplogyeeNamesByType(string _employeeType)
        {
            List<string> result = new List<string>();
            string query = string.Format("Select name from employee, employee_type where employee.employee_type_id = employee_type.id and employee_type.type_name = '{0}'", _employeeType);
            DataSet ds = accessdb.ExeQuery(query);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string employeeName = row[0].ToString();
                result.Add(employeeName);
            }
            return result;
        }

        /// <summary>
        /// Get list ids of items
        /// </summary>
        /// <returns>List of item ids as strings</returns>
        public List<string> GetListItemIds()
        {
            List<string> result = new List<string>();
            DataSet ds = accessdb.ExeQuery("select id from item");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(row[0].ToString());
            }
            return result;
        }

        /// <summary>
        /// Get list names of items
        /// </summary>
        /// <returns>List of item names as strings</returns>
        public List<string> GetListItemNames()
        {
            List<string> result = new List<string>();
            DataSet ds = accessdb.ExeQuery("select name from item");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(row[0].ToString());
            }
            return result;
        }

        /// <summary>
        /// Get Item object by Item id
        /// </summary>
        /// <param name="id">The item id</param>
        /// <returns>ItemEntry object</returns>
        public ItemEntry GetItem(int id)
        {
            DataSet ds = accessdb.ExeQuery(string.Format("select * from item where id={0}", id));
            if (ds.Tables[0].Rows.Count > 0)
            {
                return new ItemEntry()
                {
                    ID = (int)ds.Tables[0].Rows[0]["id"],
                    CategoryId = ds.Tables[0].Rows[0]["category_id"].ToString(),
                    Code = ds.Tables[0].Rows[0]["code"].ToString(),
                    NameVN = ds.Tables[0].Rows[0]["namevn"].ToString(),
                    Name = ds.Tables[0].Rows[0]["name"].ToString(),
                    Unit = ds.Tables[0].Rows[0]["unit"].ToString(),
                    Price=float.Parse(ds.Tables[0].Rows[0]["price"].ToString()),
                    Description = ds.Tables[0].Rows[0]["description"].ToString(),
                };
            }
            return null;
        }

        public List<ItemEntry> GetItemsByName(string itemName)
        {
            List<ItemEntry> items = new List<ItemEntry>();
            DataSet ds = accessdb.ExeQuery(string.Format("select * from item where name='{0}'", itemName));
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    items.Add(new ItemEntry()
                    {
                        ID = (int)row["id"],
                        CategoryId = row["category_id"].ToString(),
                        Code = row["code"].ToString(),
                        NameVN = row["namevn"].ToString(),
                        Name = row["name"].ToString(),
                        Unit = row["unit"].ToString(),
                        Price = float.Parse(row["price"].ToString()),
                        Description = row["description"].ToString(),
                    });
                }
            }
            return items;
        }

        public List<ItemEntry> GetAllItems()
        {
            List<ItemEntry> items = new List<ItemEntry>();
            DataSet ds = accessdb.ExeQuery("select * from item");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                items.Add(new ItemEntry()
                {
                    ID = (int)row["id"],
                    CategoryId = row["category_id"].ToString(),
                    Code = row["code"].ToString(),
                    Name = row["name"].ToString(),
                    NameVN = row["namevn"].ToString(),
                    Unit = row["unit"].ToString(),
                    Price = float.Parse(row["price"].ToString()),
                    Description = row["description"].ToString(),
                });
            }
            return items;
        }

        public Dictionary<string,string> GetVIPCodes()
        {
            Dictionary<string,string> listCode =new Dictionary<string,string>();
            DataSet ds = accessdb.ExeQuery("Select card_id,full_name from customer");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string card_id = row["card_id"].ToString();
                if (!listCode.ContainsKey(card_id))
                {
                    listCode.Add(card_id, row["full_name"].ToString());
                }
                /*else
                {
                    listCode[card_id] = row["full_name"].ToString();
                }*/
            }
            return listCode;
        }

        public Dictionary<string, string> GetPhoneList()
        {
            Dictionary<string, string> phoneList = new Dictionary<string, string>();
            DataSet ds = accessdb.ExeQuery("Select phone,card_id from customer");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string phone = row["phone"].ToString();
                if (phone != "" && !phoneList.ContainsKey(phone))
                {
                    phoneList.Add(phone, row["card_id"].ToString());
                }
            }
            return phoneList;
        }

        public void Dispose()
        {  
          
        }

        public bool sql(string s) {
            try
            {
                accessdb.ExeNonQuery(s);
                return true;
            }
            catch { }
            return false;
        }


        public List<Customer> GetCustomerListByCondition(string Condition)
        {
            List<Customer> ListCustomer = new List<Customer>();
            Customer customer = new Customer(accessdb, mysqldb);
            return customer.getCustomerLocalListByCondition(Condition);
        }

        public Customer getCustomerByCardID(string CardID)
        {
            Customer tempCustomer = new Customer(accessdb,mysqldb);
            tempCustomer.getCustomerLocalByCardID(CardID);
            if (tempCustomer.card_id != null)
            {
                return tempCustomer;
            }
            else
            {
                return null;
            }
        }

        public bool AddPoint(string _card_id, int _point)
        {
            Customer tempCustomer = new Customer(accessdb,mysqldb);
            tempCustomer.getCustomerLocalByCardID(_card_id);
            if (tempCustomer.addPoint(_point))
            {
                //CustomerLog.AddPointLogLocal(tempCustomer, _point);
                LOG customerlog = new LOG(accessdb, mysqldb);
                customerlog.AddPointLogLocal(tempCustomer, _point);
                // check upgrade vip
                bool within_6_months = false;
                bool within_1_year = false;
                DateTime join_date = tempCustomer.join_date;
                within_6_months = (DateTime.Now < join_date.AddMonths(6));
                within_1_year = (DateTime.Now < join_date.AddYears(1));
                int new_point = tempCustomer.point + _point;
                if ((new_point > 180 && within_6_months) || (new_point > 360 && within_1_year))
                {
                    tempCustomer.setVIP();
                    LOG templog = new LOG(accessdb, mysqldb);
                    templog.SetVipLogLocal();
                }
                
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool GetCusOnline()
        {
            return false;
        }

        public bool PushLog()
        {
            LOG customerlog = new LOG(accessdb, mysqldb);
            List<LOG> LogList = new List<LOG>();
            LogList = customerlog.GetListNewLogLocal();
            return customerlog.UpdateToLogServer(LogList);
        }

        public bool PullData()
        {
            Customer customerserver = new Customer(accessdb2, mysqldb);
            return customerserver.UpdateCustomerLocalFromServer();
        }

        public bool UpdateServer() 
        {
            LOG log = new LOG(accessdb, mysqldb);
            return log.UpdateCustomerServer();
        }

        public bool WriteLog(Customer _customer)
        {
            //CustomerLog csLog = new CustomerLog();
            return false;
        }

    }
}
